package com.liuzhu.chaoshikucungunli.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "inventory.db";
    private static final int DATABASE_VERSION = 1;

    // 用户表
    public static final String TABLE_USER = "user";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_PHONE = "phone";
    public static final String COLUMN_ROLE = "role";
    public static final String COLUMN_COMPANY = "company";
    public static final String COLUMN_AVATAR = "avatar";
    public static final String COLUMN_NOTIFICATION = "notification";

    // 商品表
    public static final String TABLE_PRODUCT = "product";
    public static final String COLUMN_PRODUCT_ID = "id";
    public static final String COLUMN_PRODUCT_NAME = "name";
    public static final String COLUMN_PRODUCT_CODE = "code";
    public static final String COLUMN_PRODUCT_PRICE = "price";
    public static final String COLUMN_PRODUCT_QUANTITY = "quantity";
    public static final String COLUMN_PRODUCT_UNIT = "unit";
    public static final String COLUMN_PRODUCT_CATEGORY = "category";
    public static final String COLUMN_PRODUCT_DESC = "description";
    public static final String COLUMN_PRODUCT_CREATE_TIME = "create_time";
    public static final String COLUMN_PRODUCT_UPDATE_TIME = "update_time";

    // 创建用户表的SQL语句
    private static final String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_USERNAME + " TEXT NOT NULL, " +
            COLUMN_PASSWORD + " TEXT NOT NULL, " +
            COLUMN_PHONE + " TEXT NOT NULL, " +
            COLUMN_ROLE + " TEXT, " +
            COLUMN_COMPANY + " TEXT, " +
            COLUMN_AVATAR + " TEXT, " +
            COLUMN_NOTIFICATION + " INTEGER DEFAULT 1" +
            ")";

    // 创建商品表的SQL语句
    private static final String CREATE_TABLE_PRODUCT = "CREATE TABLE " + TABLE_PRODUCT + " (" +
            COLUMN_PRODUCT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_PRODUCT_NAME + " TEXT NOT NULL, " +
            COLUMN_PRODUCT_CODE + " TEXT NOT NULL UNIQUE, " +
            COLUMN_PRODUCT_PRICE + " REAL NOT NULL, " +
            COLUMN_PRODUCT_QUANTITY + " INTEGER NOT NULL DEFAULT 0, " +
            COLUMN_PRODUCT_UNIT + " TEXT NOT NULL, " +
            COLUMN_PRODUCT_CATEGORY + " TEXT, " +
            COLUMN_PRODUCT_DESC + " TEXT, " +
            COLUMN_PRODUCT_CREATE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
            COLUMN_PRODUCT_UPDATE_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP" +
            ")";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建用户表和商品表
        db.execSQL(CREATE_TABLE_USER);
        db.execSQL(CREATE_TABLE_PRODUCT);
        
        // 插入默认管理员账户
        String insertAdmin = "INSERT INTO " + TABLE_USER + 
            " (" + COLUMN_USERNAME + ", " + COLUMN_PASSWORD + ", " + COLUMN_PHONE + ", " + COLUMN_ROLE + ") " +
            "VALUES ('admin', 'admin123', '13800138000', '管理员')";
        db.execSQL(insertAdmin);
        
        // 插入测试商品数据
        String[] insertProducts = {
            "INSERT INTO " + TABLE_PRODUCT + 
            " (" + COLUMN_PRODUCT_NAME + ", " + COLUMN_PRODUCT_CODE + ", " + 
            COLUMN_PRODUCT_PRICE + ", " + COLUMN_PRODUCT_QUANTITY + ", " + 
            COLUMN_PRODUCT_UNIT + ", " + COLUMN_PRODUCT_CATEGORY + ", " +
            COLUMN_PRODUCT_DESC + ") " +
            "VALUES ('旺旺牛奶', 'P001', 5.5, 100, '盒', '饮品', '旺旺牛奶，香浓美味')",

            "INSERT INTO " + TABLE_PRODUCT + 
            " (" + COLUMN_PRODUCT_NAME + ", " + COLUMN_PRODUCT_CODE + ", " + 
            COLUMN_PRODUCT_PRICE + ", " + COLUMN_PRODUCT_QUANTITY + ", " + 
            COLUMN_PRODUCT_UNIT + ", " + COLUMN_PRODUCT_CATEGORY + ", " +
            COLUMN_PRODUCT_DESC + ") " +
            "VALUES ('可口可乐', 'P002', 3.5, 200, '瓶', '饮品', '可口可乐，冰爽畅快')",

            "INSERT INTO " + TABLE_PRODUCT + 
            " (" + COLUMN_PRODUCT_NAME + ", " + COLUMN_PRODUCT_CODE + ", " + 
            COLUMN_PRODUCT_PRICE + ", " + COLUMN_PRODUCT_QUANTITY + ", " + 
            COLUMN_PRODUCT_UNIT + ", " + COLUMN_PRODUCT_CATEGORY + ", " +
            COLUMN_PRODUCT_DESC + ") " +
            "VALUES ('面包', 'P003', 8.0, 50, '个', '食品', '新鲜出炉的面包')",

            "INSERT INTO " + TABLE_PRODUCT + 
            " (" + COLUMN_PRODUCT_NAME + ", " + COLUMN_PRODUCT_CODE + ", " + 
            COLUMN_PRODUCT_PRICE + ", " + COLUMN_PRODUCT_QUANTITY + ", " + 
            COLUMN_PRODUCT_UNIT + ", " + COLUMN_PRODUCT_CATEGORY + ", " +
            COLUMN_PRODUCT_DESC + ") " +
            "VALUES ('豆奶', 'P004', 4.5, 150, '盒', '饮品', '营养美味的豆奶')"
        };
        
        // 执行所有插入语句
        for (String sql : insertProducts) {
            db.execSQL(sql);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库时删除旧表并重新创建
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCT);
        onCreate(db);
    }
} 